package com.hvgroup.modules.sanic.service.impl;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.hvgroup.modules.sanic.constants.SysCodeEnum;
import com.hvgroup.modules.sanic.service.ChatService;
import com.hvgroup.repository.ColumnInfoRepository;
import com.hvgroup.utils.StringUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@Service
@RequiredArgsConstructor
public class ChatServiceImpl implements ChatService {

//    @PersistenceContext
//    private EntityManager em;

    private final JdbcTemplate jdbcTemplate;


    private final ColumnInfoRepository columnInfoRepository;

    @Override
    public Object processModelGeneratedSql(String modelOutStr) {
        if (StringUtils.isEmpty(modelOutStr)) {
            log.error("数据应答大模型返回结果为空");
//            throw new MyException(SysCodeEnum.C_9999);
            throw new RuntimeException(SysCodeEnum.C_9999.toString());
        }

        try {
            // 尝试按 "\n\n" 分割
            String modelOutJsonStr;
            if (modelOutStr.contains("\n\n")) {
                modelOutJsonStr = modelOutStr.substring(modelOutStr.lastIndexOf("\n\n") + 2);
            } else {
                modelOutJsonStr = modelOutStr;
            }

            // 去除 Markdown 包装（```json ... ```)
            modelOutJsonStr = modelOutJsonStr
                    .replaceAll("^```json\\n?", "")
                    .replaceAll("\\n?```$", "")
                    .trim();

            // 解析 JSON 字符串为 Map
            ObjectMapper objectMapper = new ObjectMapper();
            Map<String, Object> modelOutMap = objectMapper.readValue(modelOutJsonStr, new TypeReference<Map<String, Object>>() {
            });

            String sql = (String) modelOutMap.get("sql");

            if (StringUtils.isEmpty(sql)) {
                log.error("数据应答大模型返回SQL语句为空");
//                throw new MyException(SysCodeEnum.C_9999);
                throw new RuntimeException(SysCodeEnum.C_9999.toString());
            }

            // 执行 SQL 查询
//            List<Map<String, Object>> result = mysqlUtil.queryEx(sql);
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);

            List<String> tables = new ArrayList<>();
            tables.add("view_alarm_detail");
            Map<String, Object> tableSchema = getMultipleTablesColumnComments(tables);

            // 构建返回体
            tableSchema.put("llm", modelOutMap);
            tableSchema.put("data", result);

            return objectMapper.writer()
                    .with(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
                    .writeValueAsString(tableSchema);

        } catch (Exception e) {
            log.error("数据应答处理失败: {}, 错误: {}", modelOutStr, e.getMessage(), e);
//            throw new MyException(SysCodeEnum.C_9999);
            throw new RuntimeException(SysCodeEnum.C_9999.toString());
        }
    }

    /**
     * @param tables 表名称
     * @return {"schema":[{"tableName":"table_1","schema":[{"column":"name","comment":"姓名"}]
     *
     * @description 获取mysql表schema信息
     */
    private Map<String, Object> getMultipleTablesColumnComments(List<String> tables) {
        Map<String, Object> resultData = new HashMap<>();
        List<Map<String, Object>> schemaList = new ArrayList<>();

        for (String table : tables) {
            String sql = "SELECT COLUMN_NAME AS `column`, COLUMN_COMMENT AS `comment` "
                    + "  FROM INFORMATION_SCHEMA.COLUMNS "
                    + "  WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ";

            List<Map<String, Object>> columnsInfo = jdbcTemplate.queryForList(sql, "chat_db", table);

            Map<String, Object> tableInfo = new HashMap<>();
            tableInfo.put("tableName", table);
            tableInfo.put("schemaData", columnsInfo);
            schemaList.add(tableInfo);
        }

        resultData.put("schema", schemaList);
        return resultData;
    }

    @Override
    public String selectReportByTitle(String title) {
        try {
            String sql = "SELECT markdown FROM conv_report_info WHERE title LIKE ? ORDER BY create_time DESC LIMIT 1";
            List<Map<String, Object>> reportList = jdbcTemplate.queryForList(sql, "%" + title + "%");

            if (!reportList.isEmpty()) {
                Object raw = reportList.get(0).get("markdown");
                if (raw != null) {
                    return raw.toString()
                            .replace("```markdown", "")
                            .replace("```", "");
                }
            }
            return "";
        } catch (Exception e) {
            log.error("查询报告失败: {}", e.getMessage(), e);
//            throw new MyException(SysCodeEnum.C_9999);
        }
        return "";
    }


    @SuppressWarnings("all")
    @Override
    @Transactional(rollbackFor = Exception.class)
    @Cacheable
    public String getTableStructure() {
        String dbName = jdbcTemplate.queryForObject("SELECT DATABASE()", String.class);

        String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ?";
        List<String> tables = jdbcTemplate.queryForList(sql, new Object[]{dbName}, String.class);

        return tables.stream()
                .map(table -> {
                    String colSql = "SELECT column_name FROM information_schema.columns WHERE table_schema = ? AND table_name = ?";
                    List<String> columns = jdbcTemplate.queryForList(colSql, new Object[]{dbName, table}, String.class);
                    return table + "(" + String.join(", ", columns) + ")";
                })
                .collect(Collectors.joining(" | "));
    }
}
